{
  "cells": [
    {
      "cell_type": "raw",
      "id": "afaf8039",
      "metadata": {
        "vscode": {
          "languageId": "raw"
        }
      },
      "source": [
        "---\n",
        "sidebar_label: Sql Toolkit\n",
        "---"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "e49f1e0d",
      "metadata": {},
      "source": [
        "# SqlToolkit\n",
        "\n",
        "This will help you getting started with the [SqlToolkit](/docs/concepts/tools/#toolkits). For detailed documentation of all SqlToolkit features and configurations head to the [API reference](https://api.js.langchain.com/classes/langchain.agents_toolkits_sql.SqlToolkit.html). You can also find the documentation for the Python equivalent [here](https://python.langchain.com/docs/integrations/toolkits/sql_database/).\n",
        "\n",
        "This toolkit contains a the following tools:\n",
        "\n",
        "| Name              | Description                                                                                                                                                                                                                               |\n",
        "|-------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|\n",
        "| `query-sql`       | Input to this tool is a detailed and correct SQL query, output is a result from the database. If the query is not correct, an error message will be returned. If an error is returned, rewrite the query, check the query, and try again. |\n",
        "| `info-sql`        | Input to this tool is a comma-separated list of tables, output is the schema and sample rows for those tables. Be sure that the tables actually exist by calling list-tables-sql first! Example Input: \"table1, table2, table3\".          |\n",
        "| `list-tables-sql` | Input is an empty string, output is a comma-separated list of tables in the database.                                                                                                                                                     |\n",
        "| `query-checker`   | Use this tool to double check if your query is correct before executing it. Always use this tool before executing a query with query-sql!                                                                                                 |\n",
        "\n",
        "This toolkit is useful for asking questions, performing queries, validating queries and more on a SQL database.\n",
        "\n",
        "## Setup\n",
        "\n",
        "This example uses Chinook database, which is a sample database available for SQL Server, Oracle, MySQL, etc. To set it up, follow [these instructions](https://database.guide/2-sample-databases-sqlite/), placing the `.db` file in the directory where your code lives.\n",
        "\n",
        "If you want to get automated tracing from runs of individual tools, you can also set your [LangSmith](https://docs.smith.langchain.com/) API key by uncommenting below:\n",
        "\n",
        "```typescript\n",
        "process.env.LANGSMITH_TRACING=\"true\"\n",
        "process.env.LANGSMITH_API_KEY=\"your-api-key\"\n",
        "```\n",
        "\n",
        "### Installation\n",
        "\n",
        "This toolkit lives in the `langchain` package. You'll also need to install the `typeorm` peer dependency.\n",
        "\n",
        "```{=mdx}\n",
        "import IntegrationInstallTooltip from \"@mdx_components/integration_install_tooltip.mdx\";\n",
        "import Npm2Yarn from \"@theme/Npm2Yarn\";\n",
        "\n",
        "<IntegrationInstallTooltip></IntegrationInstallTooltip>\n",
        "\n",
        "<Npm2Yarn>\n",
        "  langchain @langchain/core typeorm\n",
        "</Npm2Yarn>\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "a38cde65-254d-4219-a441-068766c0d4b5",
      "metadata": {},
      "source": [
        "## Instantiation\n",
        "\n",
        "First, we need to define our LLM to be used in the toolkit.\n",
        "\n",
        "```{=mdx}\n",
        "import ChatModelTabs from \"@theme/ChatModelTabs\";\n",
        "\n",
        "<ChatModelTabs customVarName=\"llm\" />\n",
        "```"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 8,
      "id": "d1002b65",
      "metadata": {},
      "outputs": [],
      "source": [
        "// @lc-docs-hide-cell\n",
        "\n",
        "import { ChatOpenAI } from \"@langchain/openai\";\n",
        "\n",
        "const llm = new ChatOpenAI({\n",
        "  model: \"gpt-4o-mini\",\n",
        "  temperature: 0,\n",
        "})"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 20,
      "id": "cb09c344-1836-4e0c-acf8-11d13ac1dbae",
      "metadata": {},
      "outputs": [],
      "source": [
        "import { SqlToolkit } from \"langchain/agents/toolkits/sql\"\n",
        "import { DataSource } from \"typeorm\";\n",
        "import { SqlDatabase } from \"langchain/sql_db\";\n",
        "\n",
        "const datasource = new DataSource({\n",
        "  type: \"sqlite\",\n",
        "  database: \"../../../../../../Chinook.db\", // Replace with the link to your database\n",
        "});\n",
        "const db = await SqlDatabase.fromDataSourceParams({\n",
        "  appDataSource: datasource,\n",
        "});\n",
        "\n",
        "const toolkit = new SqlToolkit(db, llm);"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "5c5f2839-4020-424e-9fc9-07777eede442",
      "metadata": {},
      "source": [
        "## Tools\n",
        "\n",
        "View available tools:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 21,
      "id": "51a60dbe-9f2e-4e04-bb62-23968f17164a",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "[\n",
            "  {\n",
            "    name: 'query-sql',\n",
            "    description: 'Input to this tool is a detailed and correct SQL query, output is a result from the database.\\n' +\n",
            "      '  If the query is not correct, an error message will be returned.\\n' +\n",
            "      '  If an error is returned, rewrite the query, check the query, and try again.'\n",
            "  },\n",
            "  {\n",
            "    name: 'info-sql',\n",
            "    description: 'Input to this tool is a comma-separated list of tables, output is the schema and sample rows for those tables.\\n' +\n",
            "      '    Be sure that the tables actually exist by calling list-tables-sql first!\\n' +\n",
            "      '\\n' +\n",
            "      '    Example Input: \"table1, table2, table3.'\n",
            "  },\n",
            "  {\n",
            "    name: 'list-tables-sql',\n",
            "    description: 'Input is an empty string, output is a comma-separated list of tables in the database.'\n",
            "  },\n",
            "  {\n",
            "    name: 'query-checker',\n",
            "    description: 'Use this tool to double check if your query is correct before executing it.\\n' +\n",
            "      '    Always use this tool before executing a query with query-sql!'\n",
            "  }\n",
            "]\n"
          ]
        }
      ],
      "source": [
        "const tools = toolkit.getTools();\n",
        "\n",
        "console.log(tools.map((tool) => ({\n",
        "  name: tool.name,\n",
        "  description: tool.description,\n",
        "})))"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "dfe8aad4-8626-4330-98a9-7ea1ca5d2e0e",
      "metadata": {},
      "source": [
        "## Use within an agent\n",
        "\n",
        "First, ensure you have LangGraph installed:\n",
        "\n",
        "```{=mdx}\n",
        "<Npm2Yarn>\n",
        "  @langchain/langgraph\n",
        "</Npm2Yarn>\n",
        "```"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 22,
      "id": "310bf18e-6c9a-4072-b86e-47bc1fcca29d",
      "metadata": {},
      "outputs": [],
      "source": [
        "import { createReactAgent } from \"@langchain/langgraph/prebuilt\"\n",
        "\n",
        "const agentExecutor = createReactAgent({ llm, tools });"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 26,
      "id": "23e11cc9-abd6-4855-a7eb-799f45ca01ae",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "[\n",
            "  {\n",
            "    name: 'list-tables-sql',\n",
            "    args: {},\n",
            "    type: 'tool_call',\n",
            "    id: 'call_LqsRA86SsKmzhRfSRekIQtff'\n",
            "  }\n",
            "]\n",
            "Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track\n",
            "[\n",
            "  {\n",
            "    name: 'query-checker',\n",
            "    args: { input: 'SELECT * FROM Artist LIMIT 10;' },\n",
            "    type: 'tool_call',\n",
            "    id: 'call_MKBCjt4gKhl5UpnjsMHmDrBH'\n",
            "  }\n",
            "]\n",
            "The SQL query you provided is:\n",
            "\n",
            "```sql\n",
            "SELECT * FROM Artist LIMIT 10;\n",
            "```\n",
            "\n",
            "This query is straightforward and does not contain any of the common mistakes listed. It simply selects all columns from the `Artist` table and limits the result to 10 rows. \n",
            "\n",
            "Therefore, there are no mistakes to correct, and the original query can be reproduced as is:\n",
            "\n",
            "```sql\n",
            "SELECT * FROM Artist LIMIT 10;\n",
            "```\n",
            "[\n",
            "  {\n",
            "    name: 'query-sql',\n",
            "    args: { input: 'SELECT * FROM Artist LIMIT 10;' },\n",
            "    type: 'tool_call',\n",
            "    id: 'call_a8MPiqXPMaN6yjN9i7rJctJo'\n",
            "  }\n",
            "]\n",
            "[{\"ArtistId\":1,\"Name\":\"AC/DC\"},{\"ArtistId\":2,\"Name\":\"Accept\"},{\"ArtistId\":3,\"Name\":\"Aerosmith\"},{\"ArtistId\":4,\"Name\":\"Alanis Morissette\"},{\"ArtistId\":5,\"Name\":\"Alice In Chains\"},{\"ArtistId\":6,\"Name\":\"Antônio Carlos Jobim\"},{\"ArtistId\":7,\"Name\":\"Apocalyptica\"},{\"ArtistId\":8,\"Name\":\"Audioslave\"},{\"ArtistId\":9,\"Name\":\"BackBeat\"},{\"ArtistId\":10,\"Name\":\"Billy Cobham\"}]\n",
            "Here are 10 artists from your database:\n",
            "\n",
            "1. AC/DC\n",
            "2. Accept\n",
            "3. Aerosmith\n",
            "4. Alanis Morissette\n",
            "5. Alice In Chains\n",
            "6. Antônio Carlos Jobim\n",
            "7. Apocalyptica\n",
            "8. Audioslave\n",
            "9. BackBeat\n",
            "10. Billy Cobham\n"
          ]
        }
      ],
      "source": [
        "const exampleQuery = \"Can you list 10 artists from my database?\"\n",
        "\n",
        "const events = await agentExecutor.stream(\n",
        "  { messages: [[\"user\", exampleQuery]]},\n",
        "  { streamMode: \"values\", }\n",
        ")\n",
        "\n",
        "for await (const event of events) {\n",
        "  const lastMsg = event.messages[event.messages.length - 1];\n",
        "  if (lastMsg.tool_calls?.length) {\n",
        "    console.dir(lastMsg.tool_calls, { depth: null });\n",
        "  } else if (lastMsg.content) {\n",
        "    console.log(lastMsg.content);\n",
        "  }\n",
        "}"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "3a5bb5ca-c3ae-4a58-be67-2cd18574b9a3",
      "metadata": {},
      "source": [
        "## API reference\n",
        "\n",
        "For detailed documentation of all SqlToolkit features and configurations head to the [API reference](https://api.js.langchain.com/classes/langchain.agents_toolkits_sql.SqlToolkit.html)."
      ]
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": "TypeScript",
      "language": "typescript",
      "name": "tslab"
    },
    "language_info": {
      "codemirror_mode": {
        "mode": "typescript",
        "name": "javascript",
        "typescript": true
      },
      "file_extension": ".ts",
      "mimetype": "text/typescript",
      "name": "typescript",
      "version": "3.7.2"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}
